const { queryMysql } = require("../utils/queryMysqlUtil");
const { isDef } = require("../utils/utils");
const dayjs = require("dayjs");
const weekday = require("dayjs/plugin/weekday");
require("dayjs/locale/zh-cn");
dayjs.extend(weekday);
dayjs.locale("zh-cn");

exports.getRepoListByTeamID = async function (reposId) {
  const queryStr = `select chihiro_team_group.id as group_id,group_name,gitlab_name,gitlab_clone_url,chihiro_gitlab_detail.create_time,chihiro_gitlab_detail.update_time from chihiro_team_group left join chihiro_gitlab_detail on chihiro_team_group.id=chihiro_gitlab_detail.group_id where chihiro_gitlab_detail.group_id=${reposId}`;
  let { results } = await queryMysql(queryStr).catch((err) => {
    return Promise.reject(err);
  });
  return results;
};

exports.getAllRepoList = async function (reqData) {
  const queryStr = `select chihiro_gitlab_detail.id as rid,chihiro_team_group.id as group_id,group_name,gitlab_name,gitlab_clone_url,chihiro_gitlab_detail.create_time,chihiro_gitlab_detail.update_time from chihiro_team_group right join chihiro_gitlab_detail on chihiro_team_group.id=chihiro_gitlab_detail.group_id`;
  let { results } = await queryMysql(queryStr).catch((err) => {
    return Promise.reject(err);
  });
  const { pageNum, pageSize } = reqData || {};
  const len = results.length;
  if (isDef(pageNum) && isDef(pageSize)) {
    //传了分页信息
    let startIndex = pageSize * (pageNum - 1);
    let endIndex = startIndex + pageSize - 1;
    return {
      total: len, // 增加总条数
      totalPage: Math.ceil(len / pageSize),
      reposList: results.slice(startIndex, endIndex + 1),
    };
  } else {
    return {
      total: len, // 增加总条数
      totalPage: 1,
      reposList: results,
    };
  }
};

/**
 * 按组查询，组下的全部仓库
 * @return {Promise<void>}
 */
exports.getRepoListByBranchId = async function (reqData) {
  const { branchId = null } = reqData || {};

  if (branchId !== null) {
    const queryStr = `
      select
      chihiro_gitlab_detail.id as rid,
      chihiro_team_group.id as group_id,
      chihiro_team_group.group_name as group_name,
      chihiro_gitlab_detail.gitlab_name as gitlab_name,
      chihiro_gitlab_detail.gitlab_clone_url as gitlab_clone_url
      from chihiro_team_group 
      right join (
        select * 
        from chihiro_branch
        where id=${branchId}
      ) team_table
      on chihiro_team_group.branch_id=team_table.id
      
      join chihiro_gitlab_detail 
      on chihiro_team_group.id=chihiro_gitlab_detail.group_id;
    `;
    let { results } = await queryMysql(queryStr).catch((err) => {
      return Promise.reject(err);
    });
    return results;
  } else {
    return Promise.reject(new Error("branchId不能为空"));
  }
};

/**
 *查询全部仓库（无分页版）
 * @return {Promise<void>}
 */
exports.getAllRepoListWithoutPaging = async function () {
  const queryStr = `
      select
      chihiro_gitlab_detail.id as rid,
      chihiro_team_group.id as group_id,
      chihiro_team_group.group_name as group_name,
      chihiro_gitlab_detail.gitlab_name as gitlab_name,
      chihiro_gitlab_detail.gitlab_clone_url as gitlab_clone_url
      from chihiro_team_group 
      join chihiro_gitlab_detail 
      on chihiro_team_group.id=chihiro_gitlab_detail.group_id;
    `;
  let { results } = await queryMysql(queryStr).catch((err) => {
    return Promise.reject(err);
  });
  return results;
};

exports.getAllRepoListWithBranch = async function (reqData) {
  const { pageSize, pageNum } = reqData || {};
  let queryStr = "";
  if (isDef(pageNum) && isDef(pageSize)) {
    queryStr = `
        SELECT chihiro_gitlab_detail.id as rid,chihiro_branch.id AS branch_id,branch_name,chihiro_team_group.id AS group_id,group_name,gitlab_name,gitlab_clone_url,chihiro_gitlab_detail.create_time,chihiro_gitlab_detail.update_time
        FROM chihiro_branch,chihiro_team_group,chihiro_gitlab_detail
        WHERE chihiro_branch.id=chihiro_team_group.branch_id AND chihiro_team_group.id=chihiro_gitlab_detail.group_id
        ORDER BY create_time DESC
        LIMIT ${pageSize}
        OFFSET ${(pageNum - 1) * pageSize}`;
  } else {
    queryStr = `
        SELECT chihiro_gitlab_detail.id as rid,chihiro_branch.id AS branch_id,branch_name,chihiro_team_group.id AS group_id,group_name,gitlab_name,gitlab_clone_url,chihiro_gitlab_detail.create_time,chihiro_gitlab_detail.update_time
        FROM chihiro_branch,chihiro_team_group,chihiro_gitlab_detail
        WHERE chihiro_branch.id=chihiro_team_group.branch_id AND chihiro_team_group.id=chihiro_gitlab_detail.group_id`;
  }
  let allCountQueryStr = `
        SELECT COUNT(*) FROM chihiro_branch,chihiro_team_group,chihiro_gitlab_detail
        WHERE chihiro_branch.id=chihiro_team_group.branch_id AND chihiro_team_group.id=chihiro_gitlab_detail.group_id`;

  let { results } = await queryMysql(queryStr).catch((err) => {
    return Promise.reject(err);
  });
  let { results: allCount } = await queryMysql(allCountQueryStr).catch(
    (err) => {
      return Promise.reject(err);
    }
  );

  return {
    total: allCount[0]["COUNT(*)"],
    reposList: results,
  };
};

exports.addRepoInfo = async function (reposInfoParams = {}) {
  const today = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const { groupId, gitlabName, gitlabCloneUrl } = reposInfoParams;
  const queryStr = `insert into chihiro_gitlab_detail(group_id,gitlab_name,gitlab_clone_url,create_time,update_time) values(${groupId},'${gitlabName}','${gitlabCloneUrl}','${today}','${today}') `;

  let { results } = await queryMysql(queryStr).catch((err) => {
    return Promise.reject(err);
  });
  return results;
};

exports.modifyRepoInfo = async function (reposInfoParams = {}) {
  const today = dayjs().format("YYYY-MM-DD HH:mm:ss");
  const { rid, groupId, gitlabName, gitlabCloneUrl } = reposInfoParams;
  const queryStr = `update chihiro_gitlab_detail set group_id=${groupId},gitlab_name='${gitlabName}',gitlab_clone_url='${gitlabCloneUrl}',update_time='${today}' where id=${rid}`;

  let { results } = await queryMysql(queryStr).catch((err) => {
    return Promise.reject(err);
  });
  return results;
};

exports.deleteRepoByrid = async function (rid) {
  const queryStr = `delete from chihiro_gitlab_detail where id=${rid}`;
  let { results } = await queryMysql(queryStr).catch((err) => {
    return Promise.reject(err);
  });
  return results;
};

exports.searchRepoInfoByKeyword = async function (searchData) {
  const { pageSize, pageNum, searchStr } = searchData || {};
  // const queryStr=`select chihiro_gitlab_detail.id as rid,chihiro_team_group.id as group_id,group_name,gitlab_name,gitlab_clone_url,chihiro_gitlab_detail.create_time,chihiro_gitlab_detail.update_time from chihiro_team_group left join chihiro_gitlab_detail on chihiro_team_group.id=chihiro_gitlab_detail.group_id where group_name like '%${searchStr}%' or gitlab_name like '%${searchStr}%' or gitlab_clone_url like '%${searchStr}%'`

  let queryStr = null;
  if (isDef(pageNum) && isDef(pageSize)) {
    queryStr = `
            SELECT *
            FROM (SELECT chihiro_gitlab_detail.id as rid,chihiro_branch.id AS branch_id,branch_name,chihiro_team_group.id AS group_id,group_name,gitlab_name,gitlab_clone_url,chihiro_gitlab_detail.create_time,chihiro_gitlab_detail.update_time
            FROM chihiro_branch,chihiro_team_group,chihiro_gitlab_detail
            WHERE chihiro_branch.id=chihiro_team_group.branch_id AND chihiro_team_group.id=chihiro_gitlab_detail.group_id 
            ) resTable
            WHERE branch_name LIKE '%${searchStr}%' OR group_name LIKE '%${searchStr}%' OR gitlab_name LIKE '%${searchStr}%' OR gitlab_clone_url LIKE '%${searchStr}%'
            ORDER BY create_time DESC
            LIMIT ${pageSize}
            OFFSET ${(pageNum - 1) * pageSize}
        `;
  } else {
    queryStr = `
            SELECT *
            FROM (SELECT chihiro_gitlab_detail.id as rid,chihiro_branch.id AS branch_id,branch_name,chihiro_team_group.id AS group_id,group_name,gitlab_name,gitlab_clone_url,chihiro_gitlab_detail.create_time,chihiro_gitlab_detail.update_time
            FROM chihiro_branch,chihiro_team_group,chihiro_gitlab_detail
            WHERE chihiro_branch.id=chihiro_team_group.branch_id AND chihiro_team_group.id=chihiro_gitlab_detail.group_id 
            ) resTable
            WHERE branch_name LIKE '%${searchStr}%' OR group_name LIKE '%${searchStr}%' OR gitlab_name LIKE '%${searchStr}%' OR gitlab_clone_url LIKE '%${searchStr}%'
        `;
  }

  let { results } = await queryMysql(queryStr).catch((err) => {
    return Promise.reject(err);
  });

  let queryAllCountStr = `
            SELECT COUNT(*)
            FROM (SELECT chihiro_gitlab_detail.id as rid,chihiro_branch.id AS branch_id,branch_name,chihiro_team_group.id AS group_id,group_name,gitlab_name,gitlab_clone_url,chihiro_gitlab_detail.create_time,chihiro_gitlab_detail.update_time
            FROM chihiro_branch,chihiro_team_group,chihiro_gitlab_detail
            WHERE chihiro_branch.id=chihiro_team_group.branch_id AND chihiro_team_group.id=chihiro_gitlab_detail.group_id 
            ) resTable
            WHERE branch_name LIKE '%${searchStr}%' OR group_name LIKE '%${searchStr}%' OR gitlab_name LIKE '%${searchStr}%' OR gitlab_clone_url LIKE '%${searchStr}%'
            
        `;
  let { results: allCount } = await queryMysql(queryAllCountStr).catch(
    (err) => {
      return Promise.reject(err);
    }
  );
  return {
    total: allCount[0]["COUNT(*)"],
    reposList: results,
  };
};

// 查询代码统计数据(代码统计邮件发送服务专用), 天单位
exports.getCodeCountData = async function (date) {
  //date格式：2023-06-07 14:30:00
  //
  const queryStr = `
    select chihiro_code_count.create_time as create_time, chihiro_code_count.update_time as update_time, chihiro_code_count.add_num as adds, chihiro_code_count.remove_num as remove, chihiro_code_count.total_num as total, chihiro_code_count.repo_rid as id, chihiro_gitlab_detail.group_id as groupId, chihiro_gitlab_detail.gitlab_name as name, chihiro_team_group.group_name as groupName 
    from chihiro_code_count 
    left join chihiro_gitlab_detail on chihiro_code_count.repo_rid=chihiro_gitlab_detail.id 
    left join chihiro_team_group on chihiro_gitlab_detail.group_id=chihiro_team_group.id where chihiro_code_count.create_time between '${date} 00:00:00' and '${date} 23:59:59'`;
  let { results } = await queryMysql(queryStr).catch((err) => {
    return Promise.reject(err);
  });
  return results;
};

// 查询代码统计数据(代码统计邮件发送服务专用), 周单位
exports.getCodeCountDataWeeks = async function (date) {
  const startWeek = dayjs(date)
    .weekday(0)
    .startOf("day")
    .add(0, "day")
    .format("YYYY-MM-DD HH:mm:ss");
  const endWeek = dayjs(date)
    .weekday(0)
    .endOf("day")
    .add(6, "day")
    .format("YYYY-MM-DD HH:mm:ss");

  const queryStr = `
        select chihiro_code_count.create_time as create_time, chihiro_code_count.update_time as update_time, chihiro_code_count.add_num as adds, chihiro_code_count.remove_num as remove, chihiro_code_count.total_num as total, chihiro_code_count.repo_rid as id, chihiro_gitlab_detail.group_id as groupId, chihiro_gitlab_detail.gitlab_name as name 
        from 
        chihiro_code_count left join chihiro_gitlab_detail on chihiro_code_count.repo_rid=chihiro_gitlab_detail.id WHERE chihiro_code_count.create_time BETWEEN '${startWeek}' AND '${endWeek}'`;
  let { results } = await queryMysql(queryStr).catch((err) => {
    return Promise.reject(err);
  });
  return results;
};
